const connection = require('../app/database')
const { post } = require('../router/post.router')

class PostService {
	// 1.创建帖子
	async create(id, content, image_url, title) {
		const statement =
			'INSERT INTO posts (user_id, content, image_url, title) VALUES (?, ?, ?, ?)'
		const [result] = await connection.execute(statement, [
			id,
			content,
			image_url,
			title
		])
		return result
	}

	// 2.更新帖子
	async update(id, post_id, content, image_url, title) {
		const statement = `UPDATE posts SET content = ?, image_url = ?, title = ? WHERE id = ? AND user_id = ?`
		const [result] = await connection.execute(statement, [
			content,
			image_url,
			title,
			post_id,
			id
		])
		return result
	}
	// 3.删除帖子
	async remove(post_id) {
		const statement = `DELETE FROM posts WHERE id = ?`
		const [result] = await connection.execute(statement, [post_id])
		return result
	}

	// 4.查询帖子 不同条件
	async findPost(title, name, status = 'approved', limit = 10, offset = 0) {
		console.log(title)
		let baseQuery = `
    SELECT 
      p.id, p.user_id, p.content, p.image_url, p.status, p.created_at, p.updated_at, p.title,
      u.name, 
      up.avatar_url, up.bio, up.nickname, up.email, up.contact_info,
			(SELECT count(*) FROM likes WHERE post_id = p.id) as likeCount,
			(SELECT count(*) FROM favorites WHERE post_id = p.id) as collectionCount
    FROM posts p
    JOIN users u ON p.user_id = u.id
    LEFT JOIN user_profiles up ON p.user_id = up.user_id
    WHERE 1 = 1
  `
		const params = []
		let query = ''

		// 根据条件动态拼接查询语句
		if (title) {
			query += ' AND p.title LIKE ?'
			params.push(`%${title}%`)
		}
		if (name) {
			query += ' AND u.name LIKE ?'
			params.push(`%${name}%`)
		}
		if (status) {
			query += ' AND p.status = ?'
			params.push(status)
		}
		// 查询总数
		const totalCountQuery =
			`SELECT COUNT(*) AS totalCount
       FROM posts p
       JOIN users u ON p.user_id = u.id
       LEFT JOIN user_profiles up
       ON p.user_id = up.user_id
       WHERE 1 = 1` + query
		// 添加分页的 LIMIT 和 OFFSET
		query += ` LIMIT ${limit} OFFSET ${offset}`

		// 查询点赞数

		const dataQuery = baseQuery + query
		const [posts] = await connection.execute(dataQuery, params)
		const [totalCountResult] = await connection.execute(totalCountQuery, params)
		const totalCount = totalCountResult[0]['totalCount']
		return {
			list: posts,
			totalCount
		}
	}

	// 5.修改帖子状态
	async updatePostStatus(post_id, status) {
		const statement = `UPDATE posts SET status = ? WHERE id = ?`
		const [result] = await connection.execute(statement, [status, post_id])
		return result
	}

	// 6.根据id查找帖子
	async findPostById(post_id) {
		const statement = `
		SELECT 
			p.id, p.user_id, p.content, p.image_url, p.status, p.created_at, p.updated_at, p.title,
			u.name, 
			up.avatar_url, up.bio, up.nickname, up.email, up.contact_info,
			(SELECT count(*) FROM likes WHERE post_id = p.id) as likeCount,
			(SELECT count(*) FROM favorites WHERE post_id = p.id) as collectionCount
		FROM posts p
		JOIN users u ON p.user_id = u.id
		LEFT JOIN user_profiles up ON p.user_id = up.user_id WHERE p.id = ?`
		const [result] = await connection.execute(statement, [post_id])
		return result
	}

	// 7.判断是否已经点过赞
	async findIsLike(post_id, user_id) {
		const statement = `SELECT * FROM likes WHERE post_id = ? AND user_id = ?`
		const [result] = await connection.execute(statement, [post_id, user_id])
		return !!result.length
	}

	// 8.点赞或者取消点赞
	async likeOrCancelLike(post_id, user_id, isLike) {
		let statement = ''
		if (isLike) {
			statement = 'DELETE FROM likes WHERE post_id = ? AND user_id = ?'
			await connection.execute(statement, [post_id, user_id])
			return false
		} else {
			statement = 'INSERT INTO likes(post_id,user_id) VALUES(?,?)'
			await connection.execute(statement, [post_id, user_id])
			return true
		}
	}

	// 9.判断是否已经收藏过
	async findIsCollection(post_id, user_id) {
		const statement = `SELECT * FROM favorites WHERE post_id = ? AND user_id = ?`
		const [result] = await connection.execute(statement, [post_id, user_id])
		return !!result.length
	}

	// 10.收藏或者取消收藏
	async collectionOrCancelCollection(post_id, user_id, isFavority) {
		let statement = ''
		if (isFavority) {
			statement = 'DELETE FROM favorites WHERE post_id = ? AND user_id = ?'
			await connection.execute(statement, [post_id, user_id])
			return false
		} else {
			statement = 'INSERT INTO favorites(post_id,user_id) VALUES(?,?)'
			await connection.execute(statement, [post_id, user_id])
			return true
		}
	}

	// 11.根据id查看收藏了的帖子
	async queryCollectionPosts(user_id, limit = 10, offset = 0) {
		const queryStatement = `
			SELECT 
				p.id, p.user_id, p.content, p.image_url, p.status, p.created_at, p.updated_at, p.title,
				u.name, 
				up.avatar_url, up.bio, up.nickname, up.email, up.contact_info,
				(SELECT count(*) FROM likes WHERE post_id = p.id) as likeCount,
				(SELECT count(*) FROM favorites WHERE post_id = p.id) as collectionCount
			FROM favorites f
			LEFT JOIN posts p on f.post_id = p.id
			LEFT JOIN users u ON p.user_id = u.id
			LEFT JOIN user_profiles up ON p.user_id = up.user_id
			WHERE f.user_id = ?
			LIMIT ${limit} OFFSET ${offset}
		`
		const countStatement = `
			SELECT 
				COUNT(*) AS totalCount
			FROM favorites f
			LEFT JOIN posts p on f.post_id = p.id
			LEFT JOIN users u ON p.user_id = u.id
			LEFT JOIN user_profiles up ON p.user_id = up.user_id
			WHERE f.user_id = ?
			LIMIT ${limit} OFFSET ${offset}
		`
		const [posts] = await connection.execute(queryStatement, [user_id])
		const [totalCountResult] = await connection.execute(countStatement, [
			user_id
		])
		const totalCount = totalCountResult[0]['totalCount']
		return {
			list: posts,
			totalCount
		}
	}

	// 12 查询收藏的人
	async findCollectionUserById(post_id) {
		const statement = `SELECT * FROM favorites WHERE post_id = ?`
		const [res] = await connection.execute(statement, [post_id])
		return res
	}

	// 13.查看点赞的人
	async findLikeUserById(post_id) {
		const statement = `SELECT * FROM likes WHERE post_id = ?`
		const [res] = await connection.execute(statement, [post_id])
		return res
	}

	// 14. 
	async findMinePost(user_id) {
		const statement = `SELECT * FROM posts WHERE user_id = ? order by created_at`
		const [res] = await connection.execute(statement, [user_id])
		return res
	}

	// 15.根据用户id查询激活的帖子
	async finePostByUserId(user_id) {
		const statement = `SELECT * FROM posts WHERE user_id = ? AND status = 'approved' order by created_at`
		const [res] = await connection.execute(statement, [user_id])
		return res
	}

}

module.exports = new PostService()
